package controllers;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import play.mvc.Controller;


import models.Barco;
import models.Usuario;

public class visualizarTripulacao extends Application {

	private static Connection conexao;
	private static Statement comando;
	
	/*************************************************************/
	
	protected static void conectar() throws SQLException {  
	    
		  
		try {
			Class.forName("org.postgresql.Driver");
		} catch (ClassNotFoundException e) {
		}

			conexao = DriverManager.getConnection(  
			        "jdbc:postgresql://localhost:5432/SGRv2",  
			        "postgres",  
			        "senha");
		
			comando =  conexao.createStatement();

	}

	/***********************************************************/
	
	public static void visualizarTripulacao(int idbarco) throws SQLException {
		
		String barcoid = "";
		try
		{		
			barcoid  =  request.querystring.toString();

			barcoid  = barcoid .substring(5);
			idbarco = Integer.parseInt(barcoid );
			
		}
		catch (Exception e)
		{
			
		}
		if(idbarco == 0)
		{
			visualizarBarcos.visualizarBarcos();			
		}
		else
		{
		Barco barco = new Barco();
		
		barco = Barco.recuperarBarco(idbarco);

		List<Usuario> tripulantes = new ArrayList<Usuario>();
		List<Usuario> tripulantesdisponiveis = new ArrayList<Usuario>();
				
		tripulantes = recuperarTripulacao(idbarco);
		
		tripulantesdisponiveis = recuperarTripulacaoDisponivel();
		
		
		
		render(idbarco, barco, tripulantes, tripulantesdisponiveis);
		
		}
	}	

    /***************************************************************/
	
	public static void inserir(int idbarco, int tripulante ) throws SQLException
	{
		conectar();
		
		comando.executeUpdate("insert into relacaobarcopessoa (" +
		"idbarco, " +
        "idusuario "+
        
        ") values (" +
        "'" +idbarco + "'," +
        "'" +tripulante + "'" +
        
		")");

		
		visualizarTripulacao(idbarco);
		
	}

    /***************************************************************/
	
    public static List<Usuario> recuperarTripulacao(int barcoid) throws SQLException
    { 
    	return Usuario.recuperarPessoas("select * from pessoa join  relacaobarcopessoa on pessoa.idusuario = relacaobarcopessoa.idusuario where relacaobarcopessoa.idbarco = " + barcoid);
    }
    
    /***************************************************************/
	
    public static List<Usuario> recuperarTripulacaoDisponivel() throws SQLException
    { 
    	return Usuario.recuperarPessoas("select * from pessoa where idusuario not in ( select idusuario from relacaobarcopessoa )");
    }
    
    /***************************************************************/
		
}
